import initialize from "./initialize";
import mydate from "./mydate";

// 定义事件数据库名称和路径
const dbName = "mydb";
const dbPath = "_doc/mydb.db";
// 定义两个表的表名称数组
const tableName = [
	"events",
	"steps",
	"devents",
	"collect",
];
const createTableOperation = [
	//构建表events的操作
	`create table if not exists events (
	    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,
	    "name" TEXT NOT NULL,
		"begin" TEXT NOT NULL,
		"end" TEXT NOT NULL,
		"description" TEXT NOT NULL,
		"label" TEXT
	)`,
	//构建表steps的操作
	`create table if not exists steps (
	    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	    "myindex" INTEGER NOT NULL,
		"date" TEXT NOT NULL,
		"description" TEXT NOT NULL
	)`,
	//构建表events的操作
	`create table if not exists devents (
	    "id" INTEGER PRIMARY KEY NOT NULL ,
	    "name" TEXT NOT NULL,
	    "begin" TEXT NOT NULL,
		"donedate" TEXT NOT NULL,
	    "end" TEXT NOT NULL,
	    "description" TEXT NOT NULL,
	    "label" TEXT
	)`,
	`create table if not exists collect (
	    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	    "content" TEXT NOT NULL,
	    "form" TEXT,
		"type" INTEGER
	)`,
];

//<------------------数据库内容--------------------------------------------------->
//-----------------------------------------------------------------------
// 打开数据库
function openDatabase(){
	let isopen = plus.sqlite.isOpenDatabase({
		name:dbName,
		path:dbPath
	});
	console.log(dbName,"数据库是否打开：",isopen);
	if(isopen == true)
	{
		return Promise.resolve();
	}
	return new Promise((resolve,reject) =>{
		console.log("执行打开操作")
		plus.sqlite.openDatabase({
			name:dbName,
			path:dbPath,
			success:() => {
				console.log("数据库打开成功");
				return resolve()},
			fail:reject,
		});
	});
};
//-----------------------------------------------------------------------
//关闭数据库
function closeDatabase(){
	let isopen = plus.sqlite.isOpenDatabase({
		name:dbName,
		path:dbName
	});
	console.log(dbName,"数据库是否打开：",isopen);
	if(isopen == false)
	{
		return Promise.reject(() =>{console.log("数据库未打开")})
	}
	new Promise((resolve,reject) =>{
		plus.sqlite.closeDatabase({
			name:dbName,
			path:dbPath,
			success:() => {
				console.log("数据库关闭成功");
				return resolve()},
			fail:reject,
		});
	});
};
//-----------------------------------------------------------------------
//查询表是否存在
async function tableExist(){
	for(let i in tableName)
	{
		let operation = "SELECT name FROM sqlite_master WHERE type='table' and name=\'"+tableName[i]+"\'";
		console.log(operation);
		let exist = await new Promise((resolve,reject)=>{
				plus.sqlite.selectSql({
				name:dbName,
				sql:operation,
				success:resolve,
				fail:reject,
			})
		})
		console.log(exist);
		if(exist.length == 0)
		{
			console.log("未发现表：“"+tableName[i]+"”，执行表的构造");
			await new Promise((resolve,reject) => {
				plus.sqlite.executeSql({
					name:dbName,
					sql:createTableOperation[i],
					success:resolve,
					fail:reject,
				})
			})
		}
		else
		{
			console.log(tableName[i]+"表已经建立，执行后续操作")
		}
	}
};
//-----------------------------------------------------------------------
//执行初始化操作
async function initializedb(){
	await openDatabase();
	await tableExist();
	initialize.giveAlert();
}
//-----------------------------------------------------------------------
//<------------------事件表内容--------------------------------------------------->
// 插入新事件
async function addEvent(obj){
	await openDatabase();
	let operation = `INSERT INTO events (name, begin, end, description, label) VALUES 
    ('${obj.name}', '${obj.begin}', '${obj.end}', '${obj.description}','')`;
	console.log("执行操作",operation);
	await new Promise((resolve,reject)=>{
		plus.sqlite.executeSql({
			name:dbName,
			sql:operation,
			success:()=>{
				console.log("插入数据",obj);
				return resolve();
			},
			fail:reject,
		});
	});
};

//<------------------事件表内容--------------------------------------------------->
// 插入新事件
async function updateEvent(obj){
	await openDatabase();
	let operation = `update events set 
	name = '${obj.name}', 
	begin = '${obj.begin}', 
	end = '${obj.end}', 
	description = '${obj.description}'
	where id = ${obj.id}`;
	console.log("执行操作",operation);
	await new Promise((resolve,reject)=>{
		plus.sqlite.executeSql({
			name:dbName,
			sql:operation,
			success:()=>{
				console.log("插入数据",obj);
				return resolve();
			},
			fail:reject,
		});
	});
};
//-----------------------------------------------------------------------
// 查询事件表
async function selectEvent(){
	await openDatabase();
	let operation = `select * from events`;
	console.log("执行操作",operation);
	let data = await new Promise((resolve,reject)=>{
		plus.sqlite.selectSql({
			name:dbName,
			sql:operation,
			success:(e)=>{
				console.log("数据项如下");
				for(let i in e)
				{
					console.log(e[i]);
				}
				return resolve(e);
			},
			fail:reject,
		});
	});
	console.log("data为");
	console.log(data);
	return data;
};
//-----------------------------------------------------------------------
// 分页查询事件表
async function selectEventByPage(page,pageSize){
	await openDatabase();
	let operation = `SELECT * FROM events ORDER BY id DESC LIMIT ${pageSize} OFFSET ${(page) * pageSize}`;
	console.log("执行操作",operation);
	let data = await new Promise((resolve,reject)=>{
		plus.sqlite.selectSql({
			name:dbName,
			sql:operation,
			success:(e)=>{
				console.log("数据项如下");
				for(let i in e)
				{
					console.log(e[i]);
				}
				return resolve(e);
			},
			fail:reject,
		});
	});
	console.log("data为");
	console.log(data);
	return data;
};
//-----------------------------------------------------------------------
// 查询事件表中的数据总数
async function selectEventsNumber(){
	await openDatabase();
	let operation = `SELECT COUNT(*) FROM events;`;
	console.log("执行操作",operation);
	let data = await new Promise((resolve,reject)=>{
		plus.sqlite.selectSql({
			name:dbName,
			sql:operation,
			success:(e)=>{
				return resolve(e);
			},
			fail:reject,
		});
	});
	console.log("个数为",data[0]["COUNT(*)"]);
	return data[0]["COUNT(*)"];
};
//-----------------------------------------------------------------------
// 进行模糊搜索
async function selectEventsByFuzzySearch(key){
	await openDatabase();
	let operation = `SELECT DISTINCT * FROM events
WHERE (name LIKE '%${key}%' OR label LIKE '%${key}%')`;
	console.log("执行操作",operation);
	let data = await new Promise((resolve,reject)=>{
		plus.sqlite.selectSql({
			name:dbName,
			sql:operation,
			success:(e)=>{
				return resolve(e);
			},
			fail:reject,
		});
	});
	console.log("模糊搜索结果为",data);
	return data;
};
//-----------------------------------------------------------------------
// 查询事件表中的姓名和起始日期
async function selectEventAndDate(){
	await openDatabase();
	let operation = `select id,name,begin,end from events`;
	console.log("执行操作",operation);
	let data = await new Promise((resolve,reject)=>{
		plus.sqlite.selectSql({
			name:dbName,
			sql:operation,
			success:(e)=>{
				console.log("数据项如下");
				for(let i in e)
				{
					console.log(e[i]);
				}
				return resolve(e);
			},
			fail:reject,
		});
	});
	console.log("data为");
	console.log(data);
	return data;
};
//-----------------------------------------------------------------------
// 删除事件表中内容，依据为唯一ID
async function deleteEvent(ID){
	await openDatabase();
	let operation = `delete from events where id=${ID}`;
	console.log("执行操作",operation);
	await new Promise((resolve,reject)=>{
		plus.sqlite.executeSql({
			name:dbName,
			sql:operation,
			success:()=>{
				console.log("数据删除成功")
				return resolve();
			},
			fail:reject,
		});
	});
};
//-----------------------------------------------------------------------
// 添加标签
async function addLabel(id,newlabel){
	await openDatabase();
	newlabel = "\'" +newlabel+",\'";
	let operation = `update events set label = COALESCE(label, '') || ${newlabel} where id=${id}`;
	console.log("执行操作",operation);
	await new Promise((resolve,reject)=>{
		plus.sqlite.executeSql({
			name:dbName,
			sql:operation,
			success:()=>{
				console.log("标签添加成功")
				return resolve();
			},
			fail:reject,
		});
	});
};
//-----------------------------------------------------------------------
// 查询单个事件
async function selectOneEvent(id){
	await openDatabase();
	let operation = `select * from events where id=${id}`;
	console.log("执行操作",operation);
	let data = await new Promise((resolve,reject)=>{
		plus.sqlite.selectSql({
			name:dbName,
			sql:operation,
			success:(e)=>{
				console.log("数据项如下");
				for(let i in e)
				{
					console.log(e[i]);
				}
				return resolve(e);
			},
			fail:reject,
		});
	});
	console.log("data为");
	console.log(data);
	return data;
};
//-----------------------------------------------------------------------
//<------------------步骤表内容--------------------------------------------------->
// 插入新步骤
async function addStep(obj){
	await openDatabase();
	console.log("插入新步骤");
	let operation = `INSERT INTO steps (myindex, date, description) VALUES 
    (${obj.myindex}, '${obj.date}', '${obj.description}')`;
	console.log("执行操作",operation);
	await new Promise((resolve,reject)=>{
		plus.sqlite.executeSql({
			name:dbName,
			sql:operation,
			success:resolve,
			fail:reject,
		});
	});
};
//-----------------------------------------------------------------------
// 查询步骤表
async function selectStep(id){
	await openDatabase();
	let operation = `select * from steps where myindex=${id}`;
	console.log("执行操作",operation);
	let data = await new Promise((resolve,reject)=>{
		plus.sqlite.selectSql({
			name:dbName,
			sql:operation,
			success:(e)=>{
				console.log("数据项如下");
				for(let i in e)
				{
					console.log(e[i]);
				}
				return resolve(e);
			},
			fail:reject,
		});
	});
	console.log("data为");
	return data;
};
//-----------------------------------------------------------------------
// 删除步骤表中内容，依据为index
async function deleteStep(index){
	await openDatabase();
	let operation = `delete from steps where myindex=${index}`;
	console.log("执行操作",operation);
	await new Promise((resolve,reject)=>{
		plus.sqlite.executeSql({
			name:dbName,
			sql:operation,
			success:()=>{
				console.log("数据删除成功")
				return resolve();
			},
			fail:reject,
		});
	});
};
//-----------------------------------------------------------------------
// 删除步骤表中内容，依据为id
async function deleteOneStep(id){
	await openDatabase();
	let operation = `delete from steps where id=${id}`;
	console.log("执行操作",operation);
	await new Promise((resolve,reject)=>{
		plus.sqlite.executeSql({
			name:dbName,
			sql:operation,
			success:()=>{
				console.log("数据删除成功")
				return resolve();
			},
			fail:reject,
		});
	});
};
//-----------------------------------------------------------------------
//<------------------完成事件表内容--------------------------------------------------->
// 插入新事件到完成事件表
async function addDoneEvent(id){
	await openDatabase();
	let data = await selectOneEvent(id);
	let obj = data[0];
	let operation = `INSERT INTO devents (id, name, begin, end, description, label, donedate) VALUES
	(${id},'${obj.name}', '${obj.begin}', '${obj.end}', '${obj.description}', '${obj.label}', '${mydate.todayString()}')`;
	console.log("执行操作",operation);
	await new Promise((resolve,reject)=>{
		plus.sqlite.executeSql({
			name:dbName,
			sql:operation,
			success:()=>{
				console.log("插入数据",obj);
				return resolve();
			},
			fail:reject,
		});
	});
	await deleteEvent(id);
};
//-----------------------------------------------------------------------
// 查询完成事件表
async function selectDoneEvent(){
	await openDatabase();
	let operation = `select * from devents`;
	console.log("执行操作",operation);
	let data = await new Promise((resolve,reject)=>{
		plus.sqlite.selectSql({
			name:dbName,
			sql:operation,
			success:(e)=>{
				console.log("数据项如下");
				for(let i in e)
				{
					console.log(e[i]);
				}
				return resolve(e);
			},
			fail:reject,
		});
	});
	console.log("data为");
	console.log(data);
	return data;
};
//<------------------事件表内容--------------------------------------------------->
// 插入新事件
async function updateDoneEvent(obj){
	await openDatabase();
	let operation = `update devents set 
	name = '${obj.name}', 
	begin = '${obj.begin}', 
	end = '${obj.end}', 
	description = '${obj.description}'
	where id = ${obj.id}`;
	console.log("执行操作",operation);
	await new Promise((resolve,reject)=>{
		plus.sqlite.executeSql({
			name:dbName,
			sql:operation,
			success:()=>{
				console.log("插入数据",obj);
				return resolve();
			},
			fail:reject,
		});
	});
};
//-----------------------------------------------------------------------
// 查询单个完成事件
async function selectOneDoneEvent(id){
	await openDatabase();
	let operation = `select * from devents where id=${id}`;
	console.log("执行操作",operation);
	let data = await new Promise((resolve,reject)=>{
		plus.sqlite.selectSql({
			name:dbName,
			sql:operation,
			success:(e)=>{
				console.log("数据项如下");
				for(let i in e)
				{
					console.log(e[i]);
				}
				return resolve(e);
			},
			fail:reject,
		});
	});
	console.log("data为");
	console.log(data);
	return data;
};
//-----------------------------------------------------------------------
// 删除完成事件表中内容，依据为唯一ID
async function deleteDoneEvent(ID){
	await openDatabase();
	let operation = `delete from devents where id=${ID}`;
	console.log("执行操作",operation);
	await new Promise((resolve,reject)=>{
		plus.sqlite.executeSql({
			name:dbName,
			sql:operation,
			success:()=>{
				console.log("数据删除成功")
				return resolve();
			},
			fail:reject,
		});
	});
};
//<------------------完成事件表内容--------------------------------------------------->
// 回调事件
async function reAddEvent(id){
	await openDatabase();
	let data = await selectOneDoneEvent(id);
	let obj = data[0];
	let operation = `INSERT INTO events (id, name, begin, end, description, label) VALUES
	(${id},'${obj.name}', '${obj.begin}', '${obj.end}', '${obj.description}', '${obj.label}')`;
	console.log("执行操作",operation);
	await new Promise((resolve,reject)=>{
		plus.sqlite.executeSql({
			name:dbName,
			sql:operation,
			success:()=>{
				console.log("插入数据",obj);
				return resolve();
			},
			fail:reject,
		});
	});
	await deleteDoneEvent(id);
};
//<-----------------收藏表内容--------------------------------------------------->
// 插入新收藏
async function addCollect(obj){
	await openDatabase();
	let operation = `INSERT INTO collect (content, form, type) VALUES 
    ("${obj.content}", '${obj.form}', ${obj.type})`;
	console.log("执行操作",operation);
	return await new Promise((resolve,reject)=>{
		plus.sqlite.executeSql({
			name:dbName,
			sql:operation,
			success:()=>{
				console.log("插入数据",obj);
				return resolve(1);
			},
			fail:reject,
		});
	});
};
//-----------------------------------------------------------------------
// 删除完成收藏表中内容，依据为content
async function deleteCollect(content){
	await openDatabase();
	let operation = `delete from collect where content='${content}'`;
	console.log("执行操作",operation);
	return await new Promise((resolve,reject)=>{
		plus.sqlite.executeSql({
			name:dbName,
			sql:operation,
			success:()=>{
				console.log("数据删除成功")
				return resolve(1);
			},
			fail:reject,
		});
	});
};
//-----------------------------------------------------------------------
// 删除完成收藏表中内容，依据为id
async function deleteCollectById(id){
	await openDatabase();
	let operation = `delete from collect where id=${id}`;
	console.log("执行操作",operation);
	return await new Promise((resolve,reject)=>{
		plus.sqlite.executeSql({
			name:dbName,
			sql:operation,
			success:()=>{
				console.log("数据删除成功")
				return resolve(1);
			},
			fail:reject,
		});
	});
};
//-----------------------------------------------------------------------
// 查询收藏
async function selectCollect(){
	await openDatabase();
	let operation = `select * from collect`;
	console.log("执行操作",operation);
	let data = await new Promise((resolve,reject)=>{
		plus.sqlite.selectSql({
			name:dbName,
			sql:operation,
			success:(e)=>{
				console.log("数据项如下");
				for(let i in e)
				{
					console.log(e[i]);
				}
				return resolve(e);
			},
			fail:reject,
		});
	});
	console.log("data为");
	console.log(data);
	return data;
};
//-----------------------------------------------------------------------
function createDatabase(){
	let isopen = plus.sqlite.isOpenDatabase({
		name:dbName,
		path:dbPath
	});
	console.log(isopen);
	if(isopen == true)
	{
		return Promise.resolve();
	}
	return new Promise((resolve,reject) =>{
		plus.sqlite.openDatabase({
			name:dbName,
			path:dbPath,
			success:resolve,
			fail:reject,
		});
	});
};
function selectSql(sql){
	console.log("查询",sql);
	return new Promise((resolve,reject) =>{
		plus.sqlite.selectSql({
			name:dbName,
			sql,
			success: resolve,
			fail:reject
		});
	});
};


export default {
	// 数据库基本内容
	openDatabase: openDatabase,       // 引用 openDatabase 函数
	closeDatabase: closeDatabase,     // 引用 closeDatabase 函数
	tableExist: tableExist,           // 引用 tableExist 函数
	createDatabase: createDatabase,   // 如果 createDatabase 是多余的，可以注释掉或删除这行
	selectSql: selectSql,             // 引用 selectSql 函数
	initializedb:initializedb,		  // 引用初始化函数
	
    // 事件表内容
	addEvent:addEvent,				  // 引用插入事件函数
	selectEvent:selectEvent,		  // 引用查询事件函数
	selectEventByPage:selectEventByPage,		 // 分页查询事件
	selectEventAndDate:selectEventAndDate, 		 // 引用查询事件和日期函数
	selectEventsNumber:selectEventsNumber, 		 // 查询总数据个数
	selectEventsByFuzzySearch:selectEventsByFuzzySearch,		// 模糊搜索
	deleteEvent:deleteEvent,		  // 引用删除事件
	addDoneEvent:addDoneEvent,		  // 引用完成事件
	addLabel:addLabel,				  // 引用添加标签
	selectOneEvent:selectOneEvent,	  // 引用查询单一事件
	updateEvent:updateEvent,		  // 引用更新单个数据
	
	// 步骤表内容
	addStep:addStep,				  // 引用插入步骤函数
	selectStep:selectStep,		      // 引用查询步骤函数
	deleteStep:deleteStep,		      // 引用删除步骤函数
	deleteOneStep:deleteOneStep,	  // 引用删除单个步骤
	
	// 完成事件表内容
	selectDoneEvent:selectDoneEvent,  // 引用查询完成事件表
	selectOneDoneEvent:selectOneDoneEvent,		 // 引用查询单一完成事件
	deleteDoneEvent:deleteDoneEvent,  // 引用删除完成事件
	reAddEvent:reAddEvent,			  // 引用回调事件函数
	updateDoneEvent:updateDoneEvent,  // 引用更新当个完成数据
	
	// 收藏表内容
	addCollect:addCollect, 			  // 引用插入收藏
	deleteCollect:deleteCollect,	  // 删除单个收藏
	selectCollect:selectCollect,	  // 获取所有收藏
	deleteCollectById:deleteCollectById, 		 // 依据ID删除唯一收藏
};